package org.wikipedia.miner.util;

import java.util.HashMap;
import java.sql.Connection;
import java.util.ArrayList;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

import org.wikipedia.miner.model.Wikipedia;

abstract public class WikiDB {
	public static String wikiconfig = "/home/nick/kss/wikipedia-miner-1.2.0/configs/test20131001.xml";
	public static Wikipedia wikipedia;

	public enum DBType {
		WIKIMINERDB, OLDWIKIMINERDB, WIKISQLDB, CNWIKISQLDB, BRITANNICADB
	};

	public static DBType dbType;

	public static String jdbcDriverName = "mysql";
	public static String dbName = "enwiki20150304";
	public static String cnDbName = "zhwiki20150325";
	public static String hostName = "localhost";
	public static String userName = "root";
	public static String password = "xxxxxxxx";

	static String jdbcUrlStr = "jdbc:" + jdbcDriverName + ":" + "//" + hostName
			+ "/" + dbName + "?" + "user=" + userName + "&" + "password="
			+ password;
	static String encodingStr = "&useUnicode=yes&characterEncoding=UTF-8&characterSetResults=UTF-8";
	static String jdbcUrl = jdbcUrlStr + encodingStr;

	static String cnJdbcUrlStr = "jdbc:" + jdbcDriverName + ":" + "//"
			+ hostName + "/" + cnDbName + "?" + "user=" + userName + "&"
			+ "password=" + password;
	static String cnJdbcUrl = cnJdbcUrlStr + encodingStr;

	public static enum PageType {
		CATEGORY, ARTICLE
	};

	public static WikiDB getDB(DBType mode) {
		try {
			switch (mode) {
			case OLDWIKIMINERDB:
				return new OldWikiMinerDB();
			case BRITANNICADB:
				return new BritannicaDB();
			case WIKISQLDB:
				return new WikiSQLDB(jdbcUrl);
			case CNWIKISQLDB:
				return new WikiSQLDB(cnJdbcUrl);
			}
		} catch (Exception e) {
		}
		return null;
	}

	/**
	 * get the total links out from a given category. It first get all
	 * categories under a given category within a given depth. Then calculate
	 * the sum of all links out from each category. return the Id of links out
	 * 
	 * @param categoryTitle
	 * @param depth
	 *            (if <=0 return)
	 * @return
	 */
	abstract public ArrayList<Integer> getCategoryLinksOut(
			String categoryTitle, int depth);

	/**
	 * given a category, and certain depth, return IDs of all distinct
	 * descendent articles
	 * 
	 * @param categoryTitle
	 * @param depth
	 *            (if <=0 return)
	 * @return
	 */
	abstract public ArrayList<Integer> getDistinctDescendentArticles(
			String categoryTitle, int depth);

	/**
	 * given a category, and certain depth, return titles of all distinct
	 * descendent articles
	 * 
	 * @param categoryTitle
	 * @param depth
	 *            (if <=0 return)
	 * @return
	 */
	abstract public ArrayList<String> getDescendentArticleTitles(
			String categoryTitle, int depth);

	/**
	 * given a category, and certain depth, return titles of all distinct
	 * descendent categories
	 * 
	 * @param categoryTitle
	 * @param depth
	 *            (if <=0 return)
	 * @return
	 */
	abstract public ArrayList<String> getDescendentCategoryTitles(
			String categoryTitle, int depth);

	/**
	 * given a page title and pagetype such as Article, Category etc) return the
	 * ID of the page
	 * 
	 * @param pageTitle
	 * @param pt
	 * @return
	 */
	abstract public int getPageId(String pageTitle, PageType pt);

	/**
	 * It returns a normalized name of a given article. The given articleTitle
	 * could be a redirect, if it is, return the targetted article title. When
	 * underlying db used is wikipedia miner, it invokes getArticleByTitle which
	 * resolves redirection automatically
	 * 
	 * @param articleTitles
	 * @return
	 */
	abstract public String getArticleTitleByTitle(String articleTitle);

	/**
	 * given an article id, return the title of the article
	 * 
	 * @param id
	 * @return
	 */
	abstract public String getArticleById(int id);

	/*
	 * given an article title, it returns the IDs of all redirects to this
	 * article.
	 */
	abstract public ArrayList<Integer> getRedirects(String articleTitle);

	/**
	 * Given the title of the article, return the titles of all articles that
	 * link out from this article
	 * 
	 * @param artTitle
	 * @param step
	 * @return
	 */
	abstract public ArrayList<String> getArticleLinksOut(String artTitle,
			int step);

	/**
	 * return all articles that link to the given article, when this article has
	 * any redirects, they are all resolved
	 * 
	 * @param articleTitle
	 * @return
	 */

	/**
	 * return titles of all articles that link into this given article
	 * 
	 * @param articleTitle
	 * @return
	 */
	abstract public ArrayList<String> getLinksIn(String articleTitle);

	/**
	 * returns IDs of all articles that link into the given article
	 * 
	 * @param articleTitle
	 * @return
	 */
	abstract public ArrayList<Integer> getArticleLinksIn(String articleTitle);

	/**
	 * given a category, first get the set of its subcategories under certain
	 * depth, and then get all the links that link into the set
	 * 
	 * 
	 */
	abstract public ArrayList<Integer> getCategoryLinksIn(String categoryTitle,
			int depth);

	/**
	 * given article title, calculate the total links into the article.
	 * 
	 * @param articleTitle
	 * @return
	 */
	abstract public int getTotalLinksInCount(String articleTitle);

	/**
	 * given a category, return all the immediate child articles of this
	 * category
	 * 
	 * @param categoryTitle
	 * @return
	 */
	abstract public ArrayList<Integer> getChildArticles(String categoryTitle);

	/**
	 * given an article, return all the immediate parent categories
	 * 
	 * @param articleTitle
	 * @param pt
	 * @return
	 */
	abstract public ArrayList<String> getParentCategoryTitles(
			String articleTitle, PageType pt);

	/**
	 * return the count of the total articles in the database
	 * 
	 * @return
	 */
	abstract public long getTotalArticleCount();

	abstract public void close();

	/**
	 * Returns the first paragraph of the article. Currently it is not
	 * implemented, so just returns the null
	 * 
	 * @param articleTitle
	 * @return
	 */
	abstract public String getFirstParagraphMarkup(String articleTitle);

	/**
	 * given a category, and IDs of a list of articles. Firstly calculate the
	 * subcategory set under certain delpth, return how many times each article
	 * are linked in from the category set.
	 * 
	 * @param categoryTitle
	 * @param depth
	 * @param toBeContainedArticleIdList
	 * @return
	 */
	public int[] countLinks(String categoryTitle, int depth,
			int[] toBeContainedArticleIdList) {
		ArrayList<Integer> lo = getCategoryLinksOut(categoryTitle, depth);
		if (toBeContainedArticleIdList == null
				|| toBeContainedArticleIdList.length == 0)
			return null;
		int[] ret = new int[toBeContainedArticleIdList.length];
		for (int i = 0; i < toBeContainedArticleIdList.length; i++) {
			ret[i] = countLinks(lo, toBeContainedArticleIdList[i]);
		}
		return ret;
	}

	/**
	 * given a list of article, and calculate how many times a given article is
	 * linked from the given article list
	 * 
	 * @param articleIdList
	 * @param articleTitle
	 * @return
	 */
	public int countLinks(ArrayList<Integer> articleIdList, String articleTitle) {
		return countLinks(articleIdList,
				getPageId(articleTitle, PageType.ARTICLE));
	}

	/**
	 * count total number of links from the given list of Articles to a given
	 * Article art
	 * 
	 * @param artList
	 * @param art
	 * @param depth
	 * @return
	 */
	public int countLinks(ArrayList<Integer> articleIdList, int id) {
		if (articleIdList == null || articleIdList.size() == 0)
			return -1;
		int count = 0;
		for (int i = 0; i < articleIdList.size(); i++) {
			if (articleIdList.get(i) == id)
				count++;
		}
		return count;
	}

}

class WikiSQLDB extends WikiDB {

	Connection conn = null;

	Connection getConnection() throws SQLException {
		return DriverManager.getConnection(jdbcUrl);
	}

	public WikiSQLDB(String jdbcUrl) throws Exception {
		this.jdbcUrl = jdbcUrl;
		conn = getConnection();
		/*
		 * conn.setClientInfo("useUnicode", "yes");
		 * conn.setClientInfo("characterEncoding", "UTF-8");
		 * conn.setClientInfo("characterSetResults", "UTF-8");
		 */
	}

	public void close() {
		try {
			conn.close();
		} catch (SQLException sqle) {
		}
	}

	public long getTotalArticleCount() {
		try {
			String getstring = "select count(*) as tc from page where page_namespace=0 and page_is_redirect=0";

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);

			while (rs.next()) {
				int id = rs.getInt("tc");
				rs.close();
				stmt.close();
				// conn.close();
				return id;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return (long) -1;
		} catch (SQLException sqle) {

		}
		return -1;
	}

	/** if the given title is a redirect, return the targeted title */
	public String getArticleTitleByTitle(String articleTitle) {
		if (articleTitle == null)
			return null;
		int[] ret = getIDandRedirect(articleTitle);
		if (ret == null) {
			return null;
		}
		if (ret[0] <= 0)
			return null;
		if (ret[1] == 0)
			return articleTitle;
		return getTargetArticle(ret[0]);
	}

	String getTargetArticle(int redirectID) {
		try {
			String getstring = "select rd_title from redirect where rd_from="
					+ redirectID;
			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				String t = rsGetString(rs, "rd_title");
				if (t != null)
					t = t.replace("_", " ");

				rs.close();
				stmt.close();
				// conn.close();
				return t;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
		} catch (SQLException sqle) {

		}

		return null;
	}

	String rsGetString(ResultSet rs, String colName) throws SQLException {
		byte[] ba = rs.getBytes(colName);
		try {
			String ret = new String(ba, "UTF-8");
			return ret;
		} catch (Exception e) {
			return null;
		}

	}

	/**
	 * give a title of category and return a list of sub category titles
	 * 
	 * @param categoryTitle
	 * @return
	 */
	public ArrayList<String> getChildCategories(String categoryTitle) {
		String catName = "\"" + categoryTitle.replace(" ", "_") + "\"";

		String queryString = "select page_title from page,categorylinks where cl_to="
				+ catName + " and cl_type='subcat' and cl_from=page_id";
		// String queryTypeString = "subcat";
		// the following query string also fetches titles of child articles
		// "select p.page_id,p.page_title from page p inner join (select cl_from,cl_to from categorylinks where cl_to="
		// +catName+" and cl_type='"+queryTypeString
		// +"') sc on sc.cl_from=p.page_id" ;

		try {
			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(queryString);
			ArrayList<String> ret = new ArrayList<String>();
			while (rs.next()) {
				String t = rsGetString(rs, "page_title");
				ret.add(t.replace("_", " "));
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {
		}

		return null;
	}

	public ArrayList<Integer> getChildArticles(String categoryTitle) {
		String catName = "\"" + categoryTitle.replace(" ", "_") + "\"";
		String queryTypeString = "page";

		String queryString = "select cl_from from categorylinks where cl_to="
				+ catName + " and cl_type='" + queryTypeString + "'";
		// the following query string also fetches titles of child articles
		// "select p.page_id,p.page_title from page p inner join (select cl_from,cl_to from categorylinks where cl_to="
		// +catName+" and cl_type='"+queryTypeString
		// +"') sc on sc.cl_from=p.page_id" ;
		try {
			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(queryString);
			ArrayList<Integer> ret = new ArrayList<Integer>();
			while (rs.next()) {
				int id = rs.getInt("cl_from");
				ret.add(id);
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {
		}

		return null;
	}

	public ArrayList<String> getParentCategoryTitles(String pageTitle,
			PageType stype) {
		int pid = getPageId(pageTitle, stype);
		String cl_type = null;
		if (stype == PageType.CATEGORY) {
			cl_type = "subcat";
		} else if (stype == PageType.ARTICLE) {
			cl_type = "page";
		} else
			return null;
		String queryString = "select cl_to from categorylinks where cl_from="
				+ pid + " and cl_type=\"" + cl_type + "\"";

		// the following query string also fetches titles of child articles
		// "select p.page_id,p.page_title from page p inner join (select cl_from,cl_to from categorylinks where cl_to="
		// +catName+" and cl_type='"+queryTypeString
		// +"') sc on sc.cl_from=p.page_id" ;
		try {
			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(queryString);
			ArrayList<String> ret = new ArrayList<String>();
			while (rs.next()) {
				String pt = rsGetString(rs, "cl_to");
				ret.add(pt.replace("_", " "));
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {
		}

		return null;
	}

	void getDescendentCategories(String categoryTitle,
			ArrayList<String> catList, int level, int returnLimit) {
		if (level <= 0)
			return;

		ArrayList<String> cl = getChildCategories(categoryTitle);

		if (cl == null || cl.size() == 0)
			return;

		for (int i = 0; i < cl.size(); i++) {
			String subcat = cl.get(i);
			if (returnLimit > 0 && catList.size() > returnLimit) {
				return;
			}
			// avoid loop
			if (categoryTitle.equals(subcat))
				continue;

			// if the category has already been parsed, do not put into the list
			// but its descendents still need to be traversed in order to avoid
			// level 3 contains more descendents than level 4
			// because of level limit, we do not have to worry about the dead
			// loop
			if (!catList.contains(subcat))
				catList.add(subcat);

			getDescendentCategories(subcat, catList, (level - 1), returnLimit);
		}
	}

	public ArrayList<Integer> getDistinctDescendentArticles(
			String categoryTitle, int level) {
		return getDescendentArticles(categoryTitle, level, true);
	}

	public ArrayList<String> getDescendentArticleTitles(String categoryTitle,
			int level) {
		ArrayList<Integer> alid = getDescendentArticles(categoryTitle, level,
				true);
		if (alid == null || alid.size() == 0)
			return null;
		ArrayList<String> ret = new ArrayList<String>();
		for (int i = 0; i < alid.size(); i++) {
			String at = getArticleById(alid.get(i));
			if (at != null)
				ret.add(at);
		}
		return ret;
	}

	public ArrayList<String> getDescendentCategoryTitles(String categoryTitle,
			int level) {
		ArrayList<String> scl = new ArrayList<String>();
		getDescendentCategories(categoryTitle, scl, level, -1);
		return scl;
	}

	ArrayList<Integer> getDescendentArticles(String categoryTitle, int level,
			boolean unique) {
		ArrayList<Integer> ret = new ArrayList<Integer>();
		ArrayList<Integer> cl = getChildArticles(categoryTitle);
		if (cl != null && cl.size() > 0)
			ret.addAll(cl);

		ArrayList<String> scl = new ArrayList<String>();
		getDescendentCategories(categoryTitle, scl, level, -1);
		if (scl == null || scl.size() == 0) {
			return ret;
		}

		for (int i = 0; i < scl.size(); i++) {
			String c = scl.get(i);
			ArrayList<Integer> al = getChildArticles(c);
			if (al == null)
				continue;
			for (int j = 0; j < al.size(); j++) {
				if (unique && ret.contains(al.get(j))) {
					continue;
				} else
					ret.add(al.get(j));
			}
		}
		return ret;
	}

	/*
	 * XXX need to combine the getArticleById into getArticleLinksIn in SQL
	 * statement
	 */
	public ArrayList<Integer> getCategoryLinksIn(String category, int level) {
		ArrayList<Integer> retlist = new ArrayList<Integer>();
		ArrayList<Integer> alist = getDistinctDescendentArticles(category,
				level);
		for (int i = 0; i < alist.size(); i++) {
			String a = getArticleById(alist.get(i));

			ArrayList<Integer> alinks = getArticleLinksIn(a);

			for (int k = 0; k < alinks.size(); k++) {
				int l = alinks.get(k);
				if (!retlist.contains(l))
					retlist.add(l);
			}
		}
		return retlist;
	}

	/**
	 * XXX need to consider the case of redirect like getLinksIn did
	 * 
	 * @param articleTitle
	 * @return
	 */
	public ArrayList<Integer> getArticleLinksIn(String articleTitle) {
		try {
			String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
			String getstring = "select pl_from,pl_namespace,pl_title,pl_from_namespace from pagelinks where pl_namespace=0 and pl_title="
					+ articleName + " and pl_from_namespace=0) ";
			// String getIntoArticle =
			// "select page_title from page,pagelinks where pl_title="+articleName+" and  pl_from_namespace=0 and pl_namespace=0 and pl_from=page_id";

			ArrayList<Integer> ret = new ArrayList<Integer>();

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				ret.add(rs.getInt("pl_from"));
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {

		}
		return null;
	}

	public int getPageId(String pageTitle, PageType pt) {
		if (pageTitle == null || pageTitle.length() == 0)
			return -1;

		int namespace = 0;
		if (pt == PageType.CATEGORY)
			namespace = 14;
		try {
			String articleName = "\"" + pageTitle.replace(" ", "_") + "\"";
			String getstring = "select page_id from page where page_namespace="
					+ namespace + " and page_title=" + articleName;

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);

			while (rs.next()) {
				int id = rs.getInt("page_id");
				rs.close();
				stmt.close();
				// conn.close();
				return id;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return -1;
		} catch (SQLException sqle) {

		}
		return -1;
	}

	int[] getIDandRedirect(String articleTitle) {
		if (articleTitle == null)
			return null;
		try {
			String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
			String getstring = "select page_id, page_is_redirect from page where page_namespace=0 and page_title="
					+ articleName;

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);

			while (rs.next()) {
				int id = rs.getInt("page_id");
				int page_red = rs.getInt("page_is_redirect");
				rs.close();
				stmt.close();
				// conn.close();
				int[] ret = { id, page_red };
				return ret;
			}

			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return null;
		} catch (SQLException sqle) {

		}
		return null;
	}

	int getCategoryId(String categoryTitle) {
		try {
			String catName = "\"" + categoryTitle.replace(" ", "_") + "\"";
			String getstring = "select cat_id from category cat_title="
					+ catName;

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				int id = rs.getInt("cat_id");
				rs.close();
				stmt.close();
				// conn.close();
				return id;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return -1;
		} catch (SQLException sqle) {

		}
		return -1;
	}

	public ArrayList<String> getArticleLinksOut(String artTitle, int step) {
		int artid = getPageId(artTitle, PageType.ARTICLE);
		ArrayList<Integer> ol = new ArrayList<Integer>();
		getArticleLinksOut(artid, step, ol);
		ArrayList<String> ret = new ArrayList<String>(ol.size());
		for (int i = 0; i < ol.size(); i++)
			ret.add(getArticleById(ol.get(i)));
		return ret;
	}

	void getArticleLinksOut(int artId, int steps, ArrayList<Integer> links) {
		if (artId <= 0 || steps <= 0)
			return;
		ArrayList<Integer> lo = getArticleLinksOut(artId);
		if (lo == null || lo.size() == 0)
			return;

		for (int i = 0; i < lo.size(); i++) {
			int aid = lo.get(i);
			if (links.contains(aid))
				continue;

			links.add(aid);
			getArticleLinksOut(aid, steps - 1, links);
		}
	}

	/**
	 * return the list of article IDs that are linked out from the given
	 * articleId. The redirected links are resolved when they are put into the
	 * returned list
	 * 
	 * @param articleId
	 * @return
	 */
	ArrayList<Integer> getArticleLinksOut(int articleId) {
		HashMap<Integer, Integer> redirects = getArticleLinksOutRedirects(articleId);
		try {
			String getstring = "select page_id from page,pagelinks where pl_from="
					+ articleId
					+ " and page_namespace=0 and pl_namespace=0 and page_title=pl_title";
			ArrayList<Integer> ret = new ArrayList<Integer>();

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				int aid = rs.getInt("page_id");
				if (redirects != null) {
					Integer t = redirects.get(aid);
					if (t != null)
						aid = t;
				}
				ret.add(aid);
			}
			rs.close();
			stmt.close();
			// conn.close();

			return ret;
		} catch (SQLException sqle) {
		}
		return null;
	}

	HashMap<Integer, Integer> getArticleLinksOutRedirects(int articleId) {
		try {
			String getstring = "select rd.rd_from,page_id from page p inner join "
					+ "(select rd_from,rd_title from page,pagelinks,redirect where pl_from="
					+ articleId
					+ " and page_namespace=0 and pl_namespace=0 and page_title=pl_title and page_id=rd_from) rd "
					+ " on p.page_title=rd.rd_title where p.page_namespace=0";

			HashMap<Integer, Integer> ret = new HashMap<Integer, Integer>();

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				int rd_from = rs.getInt("rd.rd_from");
				int target_id = rs.getInt("page_id");
				if (rd_from > 0 && target_id > 0)
					ret.put(rd_from, target_id);
			}
			rs.close();
			stmt.close();
			// conn.close();

			return ret;
		} catch (SQLException sqle) {
		}
		return null;
	}

	/*
	 * given an article id, if it is a redirect, return the target article id
	 */
	int getTargetId(int articleId) {
		try {
			String getstring = "select page_id from page,redirect where rd_from="
					+ articleId
					+ " and page_namespace=0 and rd_title=page_title";

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				int targetid = rs.getInt("page_id");
				rs.close();
				stmt.close();
				// conn.close();
				return targetid;
			}

			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return -1;
		} catch (SQLException sqle) {

		}
		return -1;
	}

	/**
	 * return the list of article IDs of links out from the given category
	 */
	public ArrayList<Integer> getCategoryLinksOut(String categoryTitle,
			int depth) {
		ArrayList<Integer> dl = getDistinctDescendentArticles(categoryTitle,
				depth);
		if (dl == null || dl.size() == 0)
			return null;
		// XXX
		// System.out.println(UniversityRanking.getTimeNow()+ " " +
		// categoryTitle + " has "+dl.size()+ " descendents");
		ArrayList<Integer> ret = new ArrayList<Integer>(dl.size());
		for (int i = 0; i < dl.size(); i++) {
			ArrayList<Integer> al = getArticleLinksOut(dl.get(i));
			if (al != null)
				ret.addAll(al);

		}
		return ret;
	}

	public String getArticleById(int id) {
		try {
			String getstring = "select page_title from page where page_namespace=0 and page_id="
					+ id;

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getstring);
			while (rs.next()) {
				String ret = rsGetString(rs, "page_title");
				ret = ret.replace("_", " ");
				rs.close();
				stmt.close();
				// conn.close();
				return ret;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
		} catch (SQLException sqle) {

		}
		return null;
	}

	/** find all articles redirecting to the given article */
	public ArrayList<Integer> getRedirects(String articleTitle) {
		try {
			String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
			String queryStr = "select rd_from redirect where and rd_title="
					+ articleName + " and rd_namespace=0";

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(queryStr);
			ArrayList<Integer> ret = new ArrayList<Integer>();
			while (rs.next()) {
				int rd = rs.getInt("rd_from");
				ret.add(rd);
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {
		}

		return null;
	}

	/**
	 * given the name of an article and return the number of links into this
	 * article
	 */
	public int getTotalLinksInCount(String articleTitle) {
		int count = getLinksInCount(articleTitle);
		int c = getLinksInRedirectCount(articleTitle);
		if (c > 0)
			count += c;
		return count;
	}

	public ArrayList<String> getLinksIn(String articleTitle) {
		ArrayList<String> ret = new ArrayList<String>();
		ArrayList<String> r1 = getLinksInNoRedirect(articleTitle);
		if (r1 != null && r1.size() > 0)
			ret.addAll(r1);
		ArrayList<String> rdl = getLinksInRedirect(articleTitle);
		if (rdl != null && rdl.size() > 0)
			ret.addAll(rdl);
		return ret;
	}

	ArrayList<String> getLinksInRedirect(String articleTitle) {
		String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
		String qstring = "select page_title from page p2 inner join "
				+ "(select pl_from from pagelinks, page, redirect where page_id=rd_from and rd_namespace=0 and page_namespace=0 and rd_title="
				+ articleName
				+ " and pl_namespace=0 and pl_from_namespace=0 and pl_title=page_title) rdl "
				+ " on rdl.pl_from=p2.page_id";
		try {
			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(qstring);
			ArrayList<String> ret = new ArrayList<String>();
			while (rs.next()) {
				String t = rsGetString(rs, "p2.page_title");
				if (t != null)
					ret.add(t.replace("_", " "));
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {
		}
		return null;
	}

	ArrayList<String> getLinksInNoRedirect(String articleTitle) {
		String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
		try {

			// String getIntoArticle =
			// "select page_title from page p inner join "
			// +"(select pl_from,pl_namespace,pl_title,pl_from_namespace from pagelinks where pl_namespace=0 and pl_title="+articleName+" and pl_from_namespace=0) e "
			// +"on e.pl_from=p.page_id";
			String getIntoArticle = "select page_title from page,pagelinks where pl_title="
					+ articleName
					+ " and  pl_from_namespace=0 and pl_namespace=0 and pl_from=page_id";

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getIntoArticle);
			ArrayList<String> ret = new ArrayList<String>();
			while (rs.next()) {
				String t = rsGetString(rs, "page_title");
				if (t != null)
					ret.add(t.replace("_", " "));
			}
			rs.close();
			stmt.close();
			// conn.close();
			return ret;
		} catch (SQLException sqle) {
		}
		return null;
	}

	int getLinksInCount(String articleTitle) {
		String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
		try {
			// String getIntoArticle =
			// "select COUNT(*) as LinksInCount from page p inner join " +
			// "(select pl_from,pl_namespace,pl_title,pl_from_namespace from pagelinks where pl_namespace=0 and pl_from_namespace=0 and pl_title="+articleName+") e on e.pl_from=p.page_id";
			String getIntoArticle = "select COUNT(*) as LinksInCount from pagelinks where pl_title="
					+ articleName
					+ " and  pl_from_namespace=0 and pl_namespace=0";

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(getIntoArticle);
			while (rs.next()) {
				int count = rs.getInt("LinksInCount");
				rs.close();
				stmt.close();
				// conn.close();
				return count;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return 0;
		} catch (SQLException sqle) {
		}
		return -1;
	}

	int getLinksInRedirectCount(String articleTitle) {
		String articleName = "\"" + articleTitle.replace(" ", "_") + "\"";
		try {
			String qstr = "select count(*) as LinksInCount from pagelinks, page, redirect "
					+ " where page_id=rd_from and rd_namespace=0 and page_namespace=0 and rd_title="
					+ articleName
					+ " and pl_namespace=0 and pl_from_namespace=0 and pl_title=page_title";

			// Connection conn = getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(qstr);
			while (rs.next()) {
				int count = rs.getInt("LinksInCount");
				rs.close();
				stmt.close();
				// conn.close();
				return count;
			}
			if (rs != null)
				rs.close();
			if (stmt != null)
				rs.close();
			// if (conn != null) conn.close();
			return 0;
		} catch (SQLException sqle) {
		}
		return -1;
	}

	public String getFirstParagraphMarkup(String articleTitle) {
		// XXX
		return null;

	}
}
